"""
Case Type   : 函数支持指定并行相关参数
Case Name   : 测试format()函数在ustore表、segment表、mot表、unlogged表中的输出
Create At   : 2024/10/16
Owner       : @kyleze
Description :
    1.创建ustore临时表,向ustore表中插入数据包含format()函数并打印输出
    2.创建segment表
    3.向segment表中插入数据包含format()函数并打印输出
    4.创建mot表
    5.向mot表中插入数据包含format()函数并打印输出
    6.创建unlogged表
    7.向unlogged表中插入数据包含format()函数并打印输出
    8.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
History     :
    modified by lvlintao at 2025/3/24，优化用例，适配MOT开关
"""
import os
import unittest

from yat.test import Node
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common


class BFormatTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.pri_node = Node('PrimaryDbBmode')
        self.constant = Constant()
        self.common = Common()
        self.table_name1 = "t_format_015_01"
        self.table_name2 = "t_format_015_02"
        self.table_name3 = "t_format_015_03"
        self.table_name4 = "t_format_015_04"
        self.default_enable_incremental_checkpoint = \
            self.common.show_param('enable_incremental_checkpoint')
        self.mot_engine = "enable_mot_server"
        text = '--------开启mot特性  ' \
               'expect:修改成功，重启数据库成功--------'
        self.log.info(text)
        self.engine_res = self.common.show_param(f'{self.mot_engine}')
        self.primary_sh.execute_gsguc('set',
                                      self.constant.GSGUC_SUCCESS_MSG,
                                      f'{self.mot_engine} = on')
        self.primary_sh.restart_db_cluster()
        status = self.primary_sh.get_db_cluster_status()
        self.log.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败:' + text)

    def test_format(self):
        step = '----step1:创建ustore临时表,向ustore表中插入数据包含format()函数' \
               '并打印输出 expect:成功----'
        self.log.info(step)
        guc_res = self.primary_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            'enable_incremental_checkpoint=off')
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + step)
        restart_res = self.primary_sh.restart_db_cluster()
        self.log.info(restart_res)
        self.assertTrue(restart_res, '执行失败:' + step)
        temp_sql = f"drop table if exists {self.table_name1};" \
                   f"create global temp table {self.table_name1}" \
                   f"(id int,num float8)with (storage_type = ustore);" \
                   f"insert into {self.table_name1} values " \
                   f"(1,format(12.456,2)), (2,format(125.52,2)), " \
                   f"(3,format('-45.123',2));" \
                   f"select * from {self.table_name1};" \
                   f"select format(num,id) from {self.table_name1};"
        insert_res = self.primary_sh.execut_db_sql(temp_sql)
        self.log.info(insert_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, insert_res,
                      '执行失败:' + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      '执行失败:' + step)
        self.assertEqual(2, insert_res.count('3 rows'), '执行失败:' + step)

        step = '----step2:创建segment表 expect:成功----'
        self.log.info(step)
        create_sql = f"drop table if exists {self.table_name2};" \
                     f"create table {self.table_name2}(id int,num float8) " \
                     f"with (segment = on);"
        create_res = self.primary_sh.execut_db_sql(create_sql)
        self.log.info(create_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, create_res,
                      '执行失败:' + step)

        step = '----step3:向segment表中插入数据包含format()函数并打印输出 ' \
               'expect:成功--'
        self.log.info(step)
        insert_sql = f"insert into {self.table_name2} values " \
                     f"(1,format(12.456,2)), (2,format(125.546,2)), " \
                     f"(3,format('456.123',2));" \
                     f"select * from {self.table_name2};" \
                     f"select format(num,id) from {self.table_name2};"
        insert_res = self.primary_sh.execut_db_sql(insert_sql)
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      '执行失败:' + step)
        self.assertEqual(2, insert_res.count('3 rows'), '执行失败:' + step)

        step = '----step4:创建mot表 expect:成功----'
        self.log.info(step)
        create_sql = f"drop foreign table if exists {self.table_name3};" \
                     f"create foreign table {self.table_name3}" \
                     f"(id int,num float8);"
        create_res = self.primary_sh.execut_db_sql(create_sql)
        self.log.info(create_res)
        self.assertIn(self.constant.CREATE_FOREIGN_SUCCESS_MSG, create_res,
                      '执行失败:' + step)

        step = '----step5:向mot表中插入数据包含format()函数并打印输出 ' \
               'expect:成功----'
        self.log.info(step)
        insert_sql = f"insert into {self.table_name3} values" \
                     f"(1,format(-12.456,2)), (2,format(25.62,2)), " \
                     f"(3,format('456.123',2));" \
                     f"select * from {self.table_name3};" \
                     f"select format(num,id) from {self.table_name3};"
        insert_res = self.primary_sh.execut_db_sql(insert_sql)
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      '执行失败:' + step)
        self.assertEqual(2, insert_res.count('3 rows'), '执行失败:' + step)

        step = '----step6:创建unlogged表 expect:成功----'
        self.log.info(step)
        create_sql = f"drop table if exists {self.table_name4};" \
                     f"create unlogged table {self.table_name4}" \
                     f"(id int,num float8);"
        create_res = self.primary_sh.execut_db_sql(create_sql)
        self.log.info(create_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, create_res,
                      '执行失败:' + step)

        step = '----step7:向unlogged表中插入数据包含format()函数并打印输出 ' \
               'expect:成功----'
        self.log.info(step)
        insert_sql = f"insert into {self.table_name4} values " \
                     f"(1,format(-23.7463,2)), (2,format(-465.465,8)), " \
                     f"(3,format('-456',2));" \
                     f"select * from {self.table_name4};" \
                     f"select format(num,id) from {self.table_name4};"
        insert_res = self.primary_sh.execut_db_sql(insert_sql)
        self.log.info(insert_res)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, insert_res,
                      '执行失败:' + step)
        self.assertEqual(2, insert_res.count('3 rows'), '执行失败:' + step)

    def tearDown(self):
        self.log.info('----清理环境----')
        drop_res1 = self.primary_sh.execut_db_sql(
            f"drop table {self.table_name1}, {self.table_name2}, "
            f"{self.table_name4};")
        self.log.info(drop_res1)
        drop_res2 = self.primary_sh.execut_db_sql(
            f"drop foreign table {self.table_name3};checkpoint;")
        self.log.info(drop_res2)
        guc_res = self.primary_sh.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'enable_incremental_checkpoint = '
            f'{self.default_enable_incremental_checkpoint}')
        self.log.info(guc_res)
        text = '--------还原mot特性  ' \
               'expect:修改成功，重启数据库成功--------'
        self.log.info(text)
        self.primary_sh.execute_gsguc('set',
                                      self.constant.GSGUC_SUCCESS_MSG,
                                      f'{self.mot_engine} = {self.engine_res}')
        node_num = self.common.get_node_num(self.pri_node)
        self.log.info(node_num)
        res_flag = self.primary_sh.check_location_consistency('primary',
                                                              node_num,
                                                              300)
        self.assertTrue(res_flag, '主备同步失败')
        restart_res = self.primary_sh.restart_db_cluster()
        self.log.info(restart_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res1, '删表失败')
        self.assertIn(self.constant.DROP_FOREIGN_SUCCESS_MSG, drop_res2,
                      '删表失败')
        self.assertTrue(guc_res, '恢复参数失败')
        self.assertTrue(restart_res, '重启失败')
        self.log.info(f'----{os.path.basename(__file__)} end----')

